from fastapi import Form
from fastapi import Request
from fastapi.responses import RedirectResponse
from psycopg.errors import UniqueViolation, ForeignKeyViolation

from .config import app, dblock, view_page
from .config import app, view_page
from .error_view import redirect_error


# from .config import dblock
# from .error_view import redirect_error


@app.get("/cousel")
async def view_student_list(request: Request):
    with dblock() as db:
        db.execute("""
        SELECT sn AS stu_sn, name as stu_name FROM student ORDER BY name
        """)
        students = list(db)

    with dblock() as db:
        db.execute("""
        SELECT cla_sn, cla_name FROM course_class ORDER BY cla_name
        """)
        classs = list(db)

    with dblock() as db:
        db.execute("""
        SELECT g.stu_sn, g.cou_sn, 
            s.name as stu_name, 
            c.name as cou_name, 
            g.grade 
        FROM course_grade as g
            INNER JOIN student as s ON g.stu_sn = s.sn
            INNER JOIN course as c  ON g.cou_sn = c.sn
        ORDER BY stu_sn, cou_sn;
        """)

        items = list(db)
    return view_page(
        request, "cousel_list.html", students=students, classs=classs, items=items
    )


@app.post("/cousel_student")
async def view_student_list(request: Request, stu_sn: int = Form(...)):
    with dblock() as db:
        db.execute("""
                SELECT student FROM class_student
                LEFT JOIN student as stu ON stu.sn = class_student.stu_sn
                WHERE cla_sn = %(cla_sn)s ;
        """)
        students = list(db)

    return view_page(
        request, "cousel_list.html", students=students
    )


@app.post("/action/cousel/add")
async def action_cousel_add(
        # stu_sn: int = Form(...), cla_sn: int = Form(...), grade: float = Form(...)
        stu_sn: int = Form(...), cla_sn: int = Form(...)
):
    try:
        with dblock() as db:
            db.execute(
                """
                INSERT INTO class_student (cla_sn, stu_sn) 
                VALUES ( %(cla_sn)s , %(stu_sn)s)
                """,
                dict(cla_sn=cla_sn, stu_sn=stu_sn),
            )
    except UniqueViolation:
        return redirect_error(f"学生{cla_sn}的课程{stu_sn}成绩已添加", return_path="/")
    except ForeignKeyViolation as ex:
        return redirect_error(f"无此学生或课程: {ex}", return_path="/")

    return RedirectResponse(url="/", status_code=302)


@app.post("/action/couselgrade/add")
async def action_cousel_add(
        stu_sn: int = Form(...), cla_sn: int = Form(...), grade: float = Form(...)
        # stu_sn: int = Form(...), cla_sn: int = Form(...)
):
    with dblock() as db:
        db.execute("""
        SELECT cou_sn FROM course_class WHERE cla_sn = %(cla_sn)s
        """,
                   dict(cla_sn=cla_sn),
                   )
        cou_sn = list(db)[0].cou_sn
        # cou_sn = db.fetchone()
    try:
        with dblock() as db:
            db.execute(
                """
                INSERT INTO course_grade (stu_sn, cou_sn, grade) 
                VALUES ( %(stu_sn)s, %(cou_sn)s, %(grade)s)
                """,
                dict(stu_sn=stu_sn, cou_sn=cou_sn, grade=grade),
            )
    except UniqueViolation:
        return redirect_error(f"学生{stu_sn}的课程{cou_sn}成绩已添加", return_path="/")
    except ForeignKeyViolation as ex:
        return redirect_error(f"无此学生或课程: {ex}", return_path="/")

    return RedirectResponse(url="/", status_code=302)
